import pandas as pd
import numpy as np


def test():
    xlsx_file = 'xlsx/demo.xlsx'
    print(pd.read_excel(xlsx_file))  # 默认只读第一个sheet
    print('=' * 50)
    print(pd.read_excel(xlsx_file, sheet_name='Sheet2'))
    print('=' * 50)
    print(pd.read_excel(pd.ExcelFile(xlsx_file), sheet_name=1))
    print('=' * 50)
    print(pd.read_excel(pd.ExcelFile(xlsx_file), sheet_name=[0, 1]))
    print('=' * 50)
    print(pd.read_excel(pd.ExcelFile(xlsx_file), header=1))  # 不包含表头，去除第一个行表头行
    print('=' * 50)
    print(pd.read_excel(pd.ExcelFile(xlsx_file), header=1, skiprows=1))  # 不包含表头，去除第一个行表头行, 跳过一行
    print('=' * 50)
    print(pd.read_excel(pd.ExcelFile(xlsx_file), index_col=0))  # 指定第一列为索引列
    print('=' * 50)
    print(pd.read_excel(xlsx_file, header=0, names=['a1', 'b1',  'c1',  'd1',  'e1',  'f1']))  # 替换第一行的表头列名为指定名称
    print('=' * 50)
    print(pd.read_excel(xlsx_file, usecols=['a', 'b']))  # 只取列a和列b
    print('=' * 50)
    print(pd.read_excel(pd.ExcelFile(xlsx_file), dtype={'a1': np.int32}))  # 指定列数据类型
    print('=' * 50)
    print(pd.read_excel(pd.ExcelFile(xlsx_file), true_values=['三']))  # 指定列数据为 三 是代表True
    print('=' * 50)
    print(pd.read_excel(pd.ExcelFile(xlsx_file), nrows=2))  # 读取两行
    print('=' * 50)
    df: pd.DataFrame = pd.read_excel(pd.ExcelFile(xlsx_file))  # 返回为DataFrame或者DataFrame的dict，包含多个dataFrame
    print(df.info())
    print('-' * 50)
    print(df.head(3))
    print('-' * 50)
    print(df.sample())
    print(df.a.astype(int).drop_duplicates().to_list())  # 获取a列并去重. drop_duplicates来自于 pandas.core.indexes.Index(base.py)
    print(type(df.a), type(df.a.astype(int)))  # pandas.core.series.Series
    print('-' * 50)
    print(df.loc[0])  # 索引第0行，数据行转列
    print('-' * 50)
    print(df.loc[[0, 2]])  # 索引第0,2行，数据
    print('-' * 50)
    print(df.loc[df['a'] > 1, ['a', 'b']])  # 定位a>1的数据, 并且只取ab列
    print('-' * 50)
    df.loc[df['a'] > 2, ['a']] = -1  # 满足条件的a列赋值为-1
    print(df)
    print('-' * 50)
    print(df.a.isin([2, 1]))  # a列值中出现2和1的行为True，其他为False
    print('-' * 50)
    data = df.loc[df.a.isin([2, 1])]
    print(data)  # 返回为True符合条件的数据
    print(type(data), df.a.sum())  # pandas.core.frame.DataFrame
    print('-' * 50)
    print(data.sort_values(by='a'))  # 返回为True符合条件的数据,并根据列排序
    print(data.sort_values(by='a').reset_index())  # 返回为True符合条件的数据,并根据列排序, 索引列重建
    print('-' * 50)
    with pd.ExcelWriter("xlsx/demo2.xlsx") as w:
        df.to_excel('xlsx/demo3.xlsx')
        pd.DataFrame(data).to_excel(w, index=False, sheet_name='hello')  # index 是否保存索引列
    print('+' * 50)
    print(df.drop_duplicates(subset=['b', 'c'], keep='first', inplace=False))  # 根据bc列去重, inplace是否直接在原数据上删除，true直接操作原始数据无数据返回，false操作副本，返回一个操作后的结果数据
    print('-' * 50)
    print(df.drop(columns=['a', 'b'], inplace=True))  # inlpace同上
    print(df)
    print('-' * 50)
    print(df.rename(columns={'c': 'c1'}, inplace=False))  # 重命名列，通过axis参数根据列名重命名，也可以重命名索引
    print('-' * 50)
    print(df.d.apply(lambda x: f'hello{x}'))  # 对d列进行数据操作，并返回结果，不是在原数据上操作
    df.d = df.d.apply(lambda x: f'hello{x}')  # 修改原列的值
    print(df)
    print('-' * 50)
    print(pd.merge(left=df, right=df.e.apply(lambda x: f'world{x}').head(2), how='right', left_on=['e'], right_on=['e']))  # 类似sql的right join
    print('-' * 50)
    rjoin_result = df.merge(right=df.e.apply(lambda x: f'world{x}').head(2), how='right', left_on=['e'], right_on=['e'])
    print(rjoin_result)  # 和上面效果一样
    print('-' * 50)
    print(rjoin_result.fillna('what', inplace=False))  # na列填充what
    print('-' * 50)
    print(rjoin_result.dropna(subset=['d', 'f'], inplace=False))  # 删除df列有NA的值
    print('-' * 50)
    for t in rjoin_result.items():
        print(type(t), len(t), type(t[0]), type(t[1]))  # <class 'tuple'> 2 <class 'str'> <class 'pandas.core.series.Series'>
        print(t[1])  # index, col_value
    print('-' * 50)


def test1():
    import os
    import calendar
    import locale
    print(calendar.monthcalendar(year=2021, month=7))
    print(calendar.LocaleTextCalendar(locale='zh_CN.UTF-8').formatweekheader(width=2).split(' '))
    # for locale.Error: unsupported locale setting
    locale.setlocale(locale.LC_ALL, 'zh_CN.UTF-8')
    locale.setlocale(locale.LC_CTYPE, 'zh_CN.UTF-8')
    demo_xlsx = os.path.join(os.path.curdir, "xlsx", "calendar.xlsx")
    demo_csv = os.path.join(os.path.curdir, "xlsx", "calendar.csv")
    print(demo_xlsx, demo_csv)
    df = pd.read_excel('xlsx/demo.xlsx')
    print(df.head())
    df.to_excel(demo_xlsx)  # why not working ??????
    # df = pd.DataFrame(np.array(calendar.monthcalendar(year=2021, month=7))
    #                   , columns=calendar.LocaleTextCalendar(locale='zh_CN.UTF-8')
    #                   .formatweekheader(width=2).split(' '))
    # df = pd.DataFrame(data={'col1': [1, 2], 'col2': [3, 4]}).astype({'col1': int, 'col2': int})
    # df.to_excel(demo_xlsx, encoding='gb2312', sheet_name='hello')
    # df.to_csv(demo_csv, encoding='gb2312')
    # with pd.ExcelWriter(demo_xlsx) as w:
    #     df = pd.DataFrame(np.array(calendar.monthcalendar(year=2021, month=7))
    #                       , columns=calendar.LocaleTextCalendar(locale='zh_CN.UTF-8')
    #                       .formatweekheader(width=2).split(' '))
    #     print(df)
    #     print(df.columns)
    #     print(df.values)
    #     df.to_excel(w, index=False, encoding='gb2312')


if __name__ == '__main__':
    test1()
